package com.Spring.SpringSpms.dao;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;




import javax.sql.DataSource;

import org.springframework.dao.DataAccessException;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.ResultSetExtractor;
import org.springframework.jdbc.core.RowMapper;

import com.Spring.SpringSpms.model.Project;

public class ProjectDAOImpl implements ProjectDAO {
private JdbcTemplate jdbcTemplate;
	
	public ProjectDAOImpl(DataSource dataSource) {
		jdbcTemplate = new JdbcTemplate(dataSource);
	}

	@Override
	public void saveOrUpdate(Project project ,String emp_ids[],String role_ids[],String platform_ids[]) {
//	System.out.println("Status id="+project.getStatus_id());
//			if (project.getProject_id() > 0) {
//			// update
//			String sql = "UPDATE Project SET project_name=?, status_id=?, client_id=?, actual_start_date=?, actual_end_date=?, planned_start_date=?, planned_end_date=?, project_description=? WHERE project_id=?";
//			//jdbcTemplate.update(sql, project.getName(),project.getSId(), project.getId());
//			jdbcTemplate.update(sql,project.getProject_name(),2, project.getClient_id(), project.getActual_start_date(), project.getActual_end_date(), project.getPlanned_start_date(), project.getPlanned_end_date(), project.getProject_description(), project.getProject_id());
//		} else {
			// insert
			String sql = "INSERT INTO Project (project_name,status_id,client_id, planned_start_date, planned_end_date, actual_start_date, actual_end_date, project_description)"
						+ " VALUES (?, ?, ?, ?, ?, ?, ?, ?)";
			//jdbcTemplate.update(sql, project.getName(),project.getSId());
			jdbcTemplate.update(sql,project.getProject_name(), 2, project.getClient_id(), project.getPlanned_start_date(), project.getPlanned_end_date(), project.getActual_start_date(), project.getActual_end_date(), project.getProject_description());
	
			String getProjectIdQuery="select project_id from project where project_name=?";
			int newProjectId=jdbcTemplate.queryForObject(getProjectIdQuery,Integer.class,project.getProject_name());
			System.out.println("In ProjectDao newly created project id is "+newProjectId);
			
			String insertEmployeeProject="insert into employee_project(emp_id,project_id) values(?,?)";
			for(int i=0;i<emp_ids.length;i++)
			{
				jdbcTemplate.update(insertEmployeeProject, Integer.parseInt(emp_ids[i]),newProjectId);
			}
		
			String insertEmployeeRole="insert into employee_role(emp_id,role_id) values(?,?)";
			for(int i=0;i<emp_ids.length;i++)
			{
				jdbcTemplate.update(insertEmployeeRole, Integer.parseInt(emp_ids[i]),Integer.parseInt(role_ids[i]));
			}

			String insertProjectPlatform="insert into project_platform(platform_id,project_id) values(?,?)";
			for(int i=0;i<platform_ids.length;i++)
			{
				jdbcTemplate.update(insertProjectPlatform, Integer.parseInt(platform_ids[i]),newProjectId);
			}

		//}
		
	}


	@Override
	public void delete(int contactId) {
		String sql = "DELETE FROM Project WHERE project_id=?";
		jdbcTemplate.update(sql, contactId);
	}

	@Override
	public List<Project> list(int emp_id) {
		String sql = "SELECT * FROM Project where project_id in(select project_id from Employee_Project where emp_id="+emp_id+")";
		List<Project> listProject = jdbcTemplate.query(sql, new RowMapper<Project>() {

			@Override
			public Project mapRow(ResultSet rs, int rowNum) throws SQLException {
				Project aContact = new Project();
				aContact.setProject_id((rs.getInt("project_id")));
				aContact.setProject_name((rs.getString("project_name")));
				return aContact;
			}
			
		});
		
		return listProject;
	}

	@Override
	public Project get(int projectId) {
		String sql = "SELECT * FROM Project WHERE project_id=" + projectId;
		return jdbcTemplate.query(sql, new ResultSetExtractor<Project>() {

			@Override
			public Project extractData(ResultSet rs) throws SQLException,
					DataAccessException {
				if (rs.next()) {
					Project contact = new Project();
					contact.setProject_id((rs.getInt("project_id")));
					contact.setProject_name((rs.getString("project_name")));
					
					return contact;
				}
				
				return null;
			}
			
		});
	}

	@Override
	public List<String> listClient(int empid) {
		String sql="select Client.client_name from Client,Project,Employee_Project where Client.client_id=Project.client_id and Employee_Project.emp_id=? and Employee_Project.project_id=Project.project_id order by Project.project_id";
		List<String> clientlist=(List<String>) jdbcTemplate.queryForList(sql,String.class,empid);		
		return clientlist;
	}


}